package org.trc.io;

import static java.sql.ResultSet.TYPE_SCROLL_SENSITIVE;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.GregorianCalendar;

import org.trc.traffic.BasicLinkTraffic;
import org.trc.traffic.AggLinkTraffic;

/**
 * 数据库更新
 * 
 * @author Lin Xu
 * 
 */

public class DBUpdate extends DBConn{
	private static final String db1 = "localhost:5432/FCD";
	private static final String db2 = "localhost:5432/vissim";
	private static final String dbUserName = "postgres";
	private static final String dbPassword = "123456";
    private static final String fcdTable = "fcd_08_11";
    private static final String basicTrafficTable = "traffic_basic";
    private static final String AggTrafficTable = "traffic_agg";
    
    private static final String vissimVehicleTable = "";
    private static final String basicVissimTrafficTable1 = "traffic_basic1";
    private static final String basicVissimTrafficTable2 = "traffic_basic2";
    private static final String basicVissimAggTrafficTable = "";
	
    
	/**
	 * load the FCD in csv format into DB (FCD_Wuhan)
	 * 
	 * TODO: populateFCDTable  populateBasicFactTable 进行比较
	 * 
	 * @param dataFileDir
	 * @param tableName
	 * @param con
	 * @throws SQLException
	 * @throws ClassNotFoundException 
	 */
	public static void populateFCDTable(File[] files, String tableName) throws SQLException, ClassNotFoundException {
		String insertSQL = "INSERT INTO " + tableName
				+ "(CARID, NUM, LINKID, PATHCLASS, LONGTITUDE, LATITUDE,"
				+ " SPEED, ANGLE, UPTIME, SECONDS, STATUS, TRIPID, GEOM)"
				+ " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ST_GeomFromText(?,?))";
		Connection connect = getPgConn(db1, dbUserName, dbPassword);
		PreparedStatement insertState = connect.prepareStatement(insertSQL);
		
		for (int i = 0; i < files.length; i++) {
			String line = null;
			String[] dataEntry = null;
			String carID = null;
			int tripID = 0;
			int curStatus = 0;
			int preStatus = 0;
			
			try {
				BufferedReader reader = new BufferedReader(new FileReader(files[i]));
				line = reader.readLine();
				dataEntry = null;
				carID = getFileName(files[i].toString()); 
				while (line != null) {
					dataEntry = line.split(",");
					insertState.setObject(1, carID); //车辆ID
					insertState.setInt(2, Integer.parseInt(dataEntry[1].trim())); // 轨迹点序号				    
				    // 道路ID
					String linkid = dataEntry[2].trim(); 
		        	if(linkid.substring(0, 2).equals("36"))
		        		linkid = "4554" + linkid.substring(2);
		        	else if(linkid.substring(0, 2).equals("46"))
		        		linkid = "4654" + linkid.substring(2);
		        	insertState.setString(3, linkid);
		        	
					insertState.setInt(4, Integer.parseInt(dataEntry[3].trim())); // 道路等级
					insertState.setDouble(5, Double.parseDouble(dataEntry[4].trim())); // 经度
					insertState.setDouble(6, Double.parseDouble(dataEntry[5].trim())); // 维度
					insertState.setDouble(7, Double.parseDouble(dataEntry[8].trim())); // 速度
					insertState.setDouble(8, Double.parseDouble(dataEntry[9].trim())); // 方向角
					
					insertState.setTimestamp(9,getTimestamp(2008, 11, 28,Integer.parseInt(dataEntry[13].trim())));//时间
					insertState.setInt(10, Integer.parseInt(dataEntry[13].trim()));
					
					// 状态
					curStatus = Integer.parseInt(dataEntry[14].trim());				
					insertState.setInt(11,curStatus); 
					
					//TRIPID				
					if( curStatus == 1 && curStatus != preStatus ){ //生成新的TRIPID
						insertState.setString(12, String.valueOf(++tripID)); 						
					}
					else if( curStatus == 1 && curStatus == preStatus ){ //延续已生成TRIPID
						insertState.setString(12, String.valueOf(tripID));					
					} else {
						insertState.setNull(12, java.sql.Types.VARCHAR);
					}
				    preStatus = curStatus;
				    
					insertState.setString(13, "POINT(" + dataEntry[4].trim() + " " + dataEntry[5].trim() + ")"); //坐标
					insertState.setInt(14, 4326);
					
					insertState.executeUpdate();
					line = reader.readLine();
				}
				reader.close();
			} catch (IOException ioe) {
				ioe.printStackTrace();
			} 
		}
		connect.close();
	}

	/**
	 * load the FCD in csv format into MySQL
	 * 
	 * @param fileURL  csv file location
	 * @param tableName db table name
	 * @param con db connection 
	 * 
	 * @throws SQLException
	 */
	public static void csvToMySQL(String fileURL, String tableName) throws SQLException {
		// SQL语句
		String insertSQL = "INSERT INTO " + tableName
				+ "(accountID, deviceID, timestamp, statusCode, latitude, longitude, speedKPH, heading)"
				+ " VALUES(?, ?, ?, ?, ?, ?, ?, ?)";		
		//SQL声明
		PreparedStatement insertState = getMySConn(db1, dbUserName, dbPassword).prepareStatement(insertSQL);
		String line = null;
		String[] dataEntry = null;
		int num = 0;
		try {
			BufferedReader reader = new BufferedReader(new FileReader(fileURL));
			line = reader.readLine();
			dataEntry = null;
			while (line != null && num < 100) {
				num++;
				dataEntry = line.split(",");
				//设定参数
				insertState.setString(1, "demo");
				insertState.setString(2, "demo3");
				insertState.setLong(3, getTimestamp(2008, 11, 28,Integer.parseInt(dataEntry[13].trim())).getTime()/1000); 
				insertState.setInt(4, 61714);
				insertState.setDouble(5,Double.parseDouble(dataEntry[5].trim())); // 维度
				insertState.setDouble(6,Double.parseDouble(dataEntry[4].trim())); // 经度
				insertState.setDouble(7,Double.parseDouble(dataEntry[8].trim())); // 速度
				insertState.setDouble(8,Double.parseDouble(dataEntry[9].trim())); // 方向角
				//执行
				insertState.executeUpdate();// SQL执行
				line = reader.readLine();
			}
		} catch (IOException ioe) {
			ioe.printStackTrace();
		}
	}	
	
	/**
	 * Read the FCD in the dataFileDir and populate into the basicSpeedAggTable (FCD_7_9)
	 * 
	 * @param dataFileDir
	 * @param tableName
	 * @param db1
	 * @param dbUserName
	 * @param dbPassword
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static void populateFcdCubeBasicFactTable(String datafileDir, Timestamp startTime, Timestamp endTime) throws SQLException, ClassNotFoundException{
		String date = datafileDir.substring(datafileDir.lastIndexOf("\\") + 1);
		
        Connection dbConn = getPgConn(db1, dbUserName, dbPassword);
        
		String insertSQL = "INSERT INTO " + "fcd_cube_basic_" + date
				+ "(CARID, LINKID, PATHCLASS, UPTIME, UPHOUR, FIVE_MINS, TEN_MINS, FIF_MINS, SPEED, STATUS, GEOM)"
				+ " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ST_GeomFromText(?,?))";
		
		PreparedStatement insertState = dbConn.prepareStatement(insertSQL);
		
		File[] files = new File(datafileDir).listFiles();
		String line = null;
		String[] dataEntry = null;
		String carID = null;
		String linkid = null;
		TimeDimension timeDim = new TimeDimension();
			
		int year = Integer.parseInt(date.substring(0, 4));		
		int month = Integer.parseInt(date.substring(4, 6));		
		int day = Integer.parseInt(date.substring(6));
		
		for (int i = 0; i < files.length; i++) {//文件遍历
			try {
				BufferedReader reader = new BufferedReader(new FileReader(files[i]));
				line = reader.readLine();
				dataEntry = null;
				carID = getFileName(files[i].toString()); 
				System.out.println("carid:"  + carID);
				while (line != null) {//行遍历
					dataEntry = line.split(",");
					insertState.setString(1, carID); //车辆ID
					linkid = dataEntry[2].trim();//匹配路段ID
		        	if(linkid.substring(0, 2).equals("36"))
		        		linkid = "4554" + linkid.substring(2);
		        	else if(linkid.substring(0, 2).equals("46"))
		        		linkid = "4654" + linkid.substring(2);
					insertState.setString(2, linkid); 	
					insertState.setString(3, dataEntry[3].trim()); //匹配路段等级
                    //时间参数解析			
					timeDim.secondsParse(year, month -1, day, Integer.parseInt(dataEntry[13].trim()));
					if(timeDim.uptimes.after(endTime) || timeDim.uptimes.before(startTime)) {
						line = reader.readLine();
						continue;
					}			
					//生成 时间参数
					insertState.setTimestamp(4,timeDim.uptimes); // 上传时间
					insertState.setInt(5, timeDim.hour); //小时
					insertState.setInt(6, timeDim.five_minsIndex); // 5分钟间隔
					insertState.setInt(7, timeDim.ten_minsIndex); //10分钟间隔标识
					insertState.setInt(8, timeDim.fif_minsIndex); //15分钟间隔表示
					insertState.setDouble(9, Double.parseDouble(dataEntry[8].trim())*3.6); //速度 (km/h)
					insertState.setInt(10, Integer.parseInt(dataEntry[14].trim())); // 载客状态
					insertState.setString(11, "POINT(" + dataEntry[4].trim() + " " + dataEntry[5].trim() + ")");
					insertState.setInt(12, 4326);
					insertState.executeUpdate();
					line = reader.readLine();
				}
			} catch (IOException ioe) {
				ioe.printStackTrace();
			}			
		}
		dbConn.close();
	}
	
	/**
	 * Group by the records in the basic_table and Insert into a new Aggregated Table
	 * 
	 * @param viewName
	 * @param tableName
	 */
	public static void populateFcdCubeAggTrafficTable(String basicFactTableName, String aggTableName, Timestamp startTime, Timestamp endTime){
		String sTimeString = startTime.toString();
		String eTimeString = endTime.toString();
		
		if(!sTimeString.substring(0, 10).equals(eTimeString.substring(0, 10))){
			System.out.println("we are not in the same day");
			return;
		}
		
		Connection dbConn;
		AggLinkTraffic[] linkTrafficDataSet = null;
		try {
			dbConn = getPgConn(db1, dbUserName, dbPassword);
			
			String querySQL = "SELECT linkid, uphour, five_mins, avg(speed) AS avg, count(DISTINCT carid) AS carnum, count(speed) AS gpsnum "
			        + "FROM " + basicFactTableName 
					+ " WHERE uptime > " + "'" + sTimeString + "'" + " AND uptime < " + "'" + eTimeString+ "'" + " AND status = 1 " 
					+ "GROUP BY linkid, uphour, five_mins HAVING count(DISTINCT carid) > 2";
			
			Statement query = dbConn.createStatement(TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			
			ResultSet result = query.executeQuery(querySQL);
			
			int rsSize = getResultSetSize(result);
			
			linkTrafficDataSet = new AggLinkTraffic[rsSize];
			
			int i = 0;
			
	        while(result.next()){
	        	linkTrafficDataSet[i] = new AggLinkTraffic();
	        	linkTrafficDataSet[i].linkID = result.getString("linkid");
	        	linkTrafficDataSet[i].hour = result.getInt("uphour");
	        	linkTrafficDataSet[i].timeInterval = result.getInt("five_mins");
	        	linkTrafficDataSet[i].avgSpeed = result.getFloat("avg");
	        	linkTrafficDataSet[i].carNum = result.getInt("carnum");
	        	linkTrafficDataSet[i].gpsNum = result.getInt("gpsnum");
				i++;
	        }
	        dbConn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		PreparedStatement insert = null;
		String insertSQL = "INSERT INTO " + aggTableName + " VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
		long date = 0;
		try {
			SimpleDateFormat dataFormat = new SimpleDateFormat("yyyy-MM-dd");
			date = dataFormat.parse(sTimeString.substring(0,10)).getTime();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		//int hour = 10;
		Date update = new Date(date);
		try {
			dbConn = getPgConn(db1, dbUserName, dbPassword);
			insert = dbConn.prepareStatement(insertSQL);
			for (int i = 0; i < linkTrafficDataSet.length; i++) {
				insert.setString(1, linkTrafficDataSet[i].linkID);
			    insert.setFloat(2, linkTrafficDataSet[i].avgSpeed); 	
			    insert.setInt(3, linkTrafficDataSet[i].getTrafficStatus()); 
			    insert.setInt(4, linkTrafficDataSet[i].carNum); 
			    insert.setInt(5, linkTrafficDataSet[i].gpsNum); 
			    insert.setInt(6, linkTrafficDataSet[i].timeInterval); 
			    insert.setInt(7, linkTrafficDataSet[i].hour); 
			    insert.setDate(8, update);
			    insert.executeUpdate();
			}
			dbConn.close();
		} catch (SQLException e) {			
			e.printStackTrace();
		} catch (ClassNotFoundException e) {			
			e.printStackTrace();
		} 
    }
	
	/**
	 * 填充单车提取的行程时间/速度
	 * 
	 * @param linkTrafficList
	 * @param tableName
	 */
	public static void populateFcdCubeBasicFactTable(ArrayList<BasicLinkTraffic> linkTrafficList, String tableName) {
		String insertSQL = "INSERT INTO " + tableName
				+ "(CARID, LINKID, TRAVELTIME, TRAVELSPEED, FIVE_MINS, UPHOUR, UPDAY)"
				+ " VALUES(?, ?, ?, ?, ?, ?, ?)";
		Connection connect = null;
		try {
			connect = getPgConn(db1, dbUserName, dbPassword);
			PreparedStatement insertState = connect.prepareStatement(insertSQL);
			for(BasicLinkTraffic linkTraffic : linkTrafficList) {
				insertState.setString(1, linkTraffic.carID);
				insertState.setString(2, linkTraffic.linkID);
				insertState.setInt(3, linkTraffic.travelTime);
				insertState.setFloat(4, linkTraffic.travelSpeed);
				insertState.setInt(5, linkTraffic.enterTime % 3600 / 300 + 1);
				insertState.setInt(6, linkTraffic.enterTime / 3600);
				insertState.setDate(7, new Date(linkTraffic.timestamp.getTimeInMillis()));
				insertState.executeUpdate();
		    }
			connect.close();
		  } catch (SQLException e) {
				e.printStackTrace();
	      } catch (ClassNotFoundException e) {
			e.printStackTrace();
		  }
	}
	
	//-----------------------------------------------------------------------------------------------------//
	/**
	 * 填充仿真车辆提取的行程时间/速度
	 * 
	 * @param linkTrafficList
	 * @param tableName
	 */
	public static void populateVissimBasicTrafficTable1(ArrayList<BasicLinkTraffic> linkTrafficList) {
		String insertSQL = "INSERT INTO " + basicVissimTrafficTable1
				+ "(CARID, LINKID, TRAVELTIME, TRAVELSPEED, FIVE_MINS)" + " VALUES(?, ?, ?, ?, ?)";
		Connection connect = null;
		try {
			connect = getPgConn(db2, dbUserName, dbPassword);
			PreparedStatement insertState = connect.prepareStatement(insertSQL);
			for(BasicLinkTraffic linkTraffic : linkTrafficList) {
				insertState.setString(1, linkTraffic.carID);
				insertState.setString(2, linkTraffic.linkID);
				insertState.setInt(3, linkTraffic.travelTime);
				insertState.setFloat(4, linkTraffic.travelSpeed);
				insertState.setInt(5, (linkTraffic.enterTime - 2000) / 300 + 1);
				insertState.executeUpdate();
		    }
			connect.close();
		  } catch (SQLException e) {
				e.printStackTrace();
	      } catch (ClassNotFoundException e) {
			e.printStackTrace();
		  }
	}
	
	public static void populateVissimBasicTrafficTable2(ArrayList<BasicLinkTraffic> linkTrafficList) {
		String insertSQL = "INSERT INTO " + basicVissimTrafficTable2
				+ "(CARID, LINKID, TRAVELSPEED, WEIGHT, FIVE_MINS)" + " VALUES(?, ?, ?, ?, ?)";
		Connection connect = null;
		try {
			connect = getPgConn(db2, dbUserName, dbPassword);
			PreparedStatement insertState = connect.prepareStatement(insertSQL);
			for(BasicLinkTraffic linkTraffic : linkTrafficList) {
				insertState.setString(1, linkTraffic.carID);
				insertState.setString(2, linkTraffic.linkID);				
				insertState.setFloat(3, linkTraffic.travelSpeed);
				insertState.setFloat(4, linkTraffic.weight);
				insertState.setInt(5, (linkTraffic.enterTime - 2000) / 300 + 1);
				insertState.executeUpdate();
		    }
			connect.close();
		  } catch (SQLException e) {
				e.printStackTrace();
	      } catch (ClassNotFoundException e) {
			e.printStackTrace();
		  }
	}
	
	/**
	 * 填充仿真浮动车表
	 * 
	 * @param fileURL
	 * @param tableName
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static void populateVissimVehicleTable(String fileURL, String tableName) throws SQLException, ClassNotFoundException{
        Connection dbConn = getPgConn(db2, dbUserName, dbPassword);
        
		String insertSQL = "INSERT INTO " + tableName 
				+ "(CARID, START_TIME, UP_TIME, FIVE_MINS, LINKID, DISTANCE, X, Y, SPEED)"
				+ " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
		PreparedStatement insertState = dbConn.prepareStatement(insertSQL);
			
	    try {
	    	BufferedReader reader = new BufferedReader(new FileReader(fileURL));
		    String line = reader.readLine();
		    String[] dataEntry = null;
		    int startTime = 0;
		    int upTime = 0;
		    while (line != null) {//行遍历
		    	dataEntry = line.split(";");
		    	//进行40s采样
		    	startTime = (int)Float.parseFloat(dataEntry[3].trim());
		    	upTime = (int)Float.parseFloat(dataEntry[5].trim());
		    	if((upTime - startTime)%40 != 0){
		    		line = reader.readLine();
		    		continue;
		    	}
		    	insertState.setString(1, dataEntry[0].trim()); //carid
		    	insertState.setInt(2, startTime); // starttime
		    	insertState.setInt(3, upTime); // uptime    	
		    	insertState.setInt(4, (upTime-2000)/300 + 1); //time_index 	
		    	insertState.setString(5, dataEntry[1].trim()); // linkid
		    	insertState.setFloat(6, Float.parseFloat(dataEntry[2].trim())); // distance
		    	insertState.setFloat(7, Float.parseFloat(dataEntry[6].trim())); // x
		    	insertState.setFloat(8, Float.parseFloat(dataEntry[7].trim())); // y
		    	insertState.setFloat(9, Float.parseFloat(dataEntry[4].trim())); //speed

				insertState.executeUpdate();
				line = reader.readLine();
				}
		} catch (IOException ioe) {
			ioe.printStackTrace();
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	    
	    dbConn.close();
	}
	
	//-----------------------------------------------------------------------------------------------------//
	/**
	 * Date type style Transform
	 * 
	 * @param year
	 * @param month
	 * @param day
	 * @param seconds
	 * @return
	 */
	private static Timestamp getTimestamp(int year, int month, int day, int seconds) {
		GregorianCalendar cal = new GregorianCalendar(year, month-1, day, seconds / 3600, seconds % 3600 / 60, seconds % 3600 % 60);
		return new Timestamp(cal.getTimeInMillis());
	}

	/**
	 * Parse the fileName
	 * 
	 * @param filePathName
	 * @return
	 */
	private static String getFileName(String filePathName) {
		String[] address = filePathName.split("\\\\");
		String[] fileName = address[address.length - 1].split("\\.");
		return fileName[0];
	}
	
	public static void main(String[] args) throws SQLException,
			ClassNotFoundException {	
     	//String csvDir = "E:\\Data\\FCD\\20090310";
		
		String[] csvDirList = new String[7];
		
		csvDirList[0] = "D:\\GISDatabase\\WMFCD\\20090308";
		csvDirList[1] = "D:\\GISDatabase\\WMFCD\\20090309";
		csvDirList[2] = "D:\\GISDatabase\\WMFCD\\20090310";
		csvDirList[3] = "D:\\GISDatabase\\WMFCD\\20090311";
		csvDirList[4] = "D:\\GISDatabase\\WMFCD\\20090312";
		csvDirList[5] = "D:\\GISDatabase\\WMFCD\\20090313";
		csvDirList[6] = "D:\\GISDatabase\\WMFCD\\20090314";
     	
		float cost = 0f; // 聚集,载入的开销
		
		for(String  csvDir : csvDirList){
			
		String date = csvDir.substring(csvDir.lastIndexOf("\\") + 1);
			
		int year = Integer.parseInt(date.substring(0, 4));		
		int month = Integer.parseInt(date.substring(4, 6));		
		int day = Integer.parseInt(date.substring(6));
		
		Timestamp startTime = getTimestamp(year, month, day, 0*3600);
		Timestamp endTime =  getTimestamp(year, month, day, 24*3600-1);
		
		System.out.println(startTime.toString());
		System.out.println(endTime.toString());
		
		populateFcdCubeBasicFactTable(csvDir, startTime, endTime); // 载入基本事实表

		float current = System.currentTimeMillis();
		
		populateFcdCubeAggTrafficTable("fcd_cube_basic_" + csvDir.substring(csvDir.lastIndexOf("\\") + 1), "fcd_cube_agg", startTime, endTime); //聚集表
		
		cost += (System.currentTimeMillis() - current);
		
		}
		
		System.out.println("Traffic cube construction cost: " + cost);
	
		//DBUpdate.populateVissimVehicleTable("E:\\Data\\Simulation\\MovingCluster\\mc_roadnet.fzp", "vehicle_40s");
			
	}

}

class TimeDimension {
	public Timestamp uptimes;
	
	public int year;
	public int month;
	public int day;
	public int hour;
	public int mins;
	public int seconds;	
	public int five_minsIndex;
	public int ten_minsIndex;
	public int fif_minsIndex;
	
	public void secondsParse(int year, int month, int day, int secondsInDay){
		this.year = year;
		this.month = month;
		this.day = day;
		this.hour = secondsInDay / 3600;
		this.mins = secondsInDay % 3600 / 60;
		this.seconds = secondsInDay % 3600 % 60;	
		this.five_minsIndex = secondsInDay % 3600 / 300 + 1;
	    this.ten_minsIndex = secondsInDay % 3600 / 600 + 1;
	    this.fif_minsIndex = secondsInDay % 3600 / 900 + 1;	    
		GregorianCalendar calendar = new GregorianCalendar(this.year, this.month, this.day, hour, mins, seconds);
		this.uptimes =  new Timestamp(calendar.getTimeInMillis());
	}
}

